This is part 1 of my Tips and Tricks blogs for my upcoming book project.

For all of my professional writing career, I have been forced to be on the top of my formatting game when writing code. Uppercasing this, spacing this, aligning that, formatting code just right. In my personal life, I also try to format my code in a readable manner, though I do format code differently (I pretty much LOATHE uppercase characters), but I work really hard to make code readable so my coworkers don’t need maze traversal skills to read my code (or Red-Gate SQL Prompt to reformat).

It has long been a habit that I name my constraints, and even if it wasn’t useful for database comparisons, it just helps me to see the database structure all that much eaiser. The fact that I as I get more experience writing SQL and about SQL, I have grown to habitually format my code a certain way makes it all the more interesting to me that I had never come across this scenario to not name constraints.

Consider the following table structure:

CREATE TABLE #testName
(
    testNameId int CONSTRAINT PKTestName PRIMARY KEY
)

Create it on one connection, in the context of any database. Seems harmless enough, right? Executes, works just fine. Now, without dropping that table, go to another connection. Run the code again.

Msg 2714, Level 16, State 5, Line 1
There is already an object named ‘PKTestName’ in the database.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint or index. See previous errors.

Well, doesn’t that just beat all. Looking in the system tables (I won’t lie, I had to go back and capitalize SELECT, FROM and WHERE to prove my earlier point abut formatting):

SELECT name, object_id, type
FROM   tempdb.sys.objects
WHERE  name = ‘PKTestName’

You will see it right there in the tables:

name           object_id   type
————– ———– —-
PKTestName     -1450612125 PK

If you want to check to see if the table exists only once, run the following query:

SELECT name
FROM   tempdb.sys.objects
WHERE  name like ‘#testName%’

You will get back something like:

name                                                                                                                           
——————————————————————————————————————————–
#testName___________________________________________________________________________________________________________000000000009

Now, changing the create table query to:

CREATE TABLE #testName
(
    testNameId int PRIMARY KEY
)

Re-execute the query on sys.objects for the temp table and you will see two rows with a root name of #testName.

name
——————————————————————————————————————————–
#testName___________________________________________________________________________________________________________000000000009
#testName___________________________________________________________________________________________________________00000000000B

So the answer to the question of is it ever better to not name your constraints is “yes”, but only when your table will not outlive your connection. Clearly you don’t want to be stuck with names like: PK__#testNam__3EE579F50DAE3402 which is the delightful name that was assigned the primary key object the second time. The third time: PK__#testNam__3EE579F5088FC982, which is just plain ugly.

Bonus points if you thought, can’t you just make the constraint name a temporary name by putting a # in front like so?

CREATE TABLE #testName
(
    testNameId int CONSTRAINT #PKTestName PRIMARY KEY
)

It doesn’t work, but it was a good idea.

Msg 8166, Level 16, State 0, Line 1
Constraint name ‘#PKTestName’ not permitted. Constraint names cannot begin with a number sign (#).